/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2021/10/26 9:57:27                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('UserApply') and o.name = 'FK_USERAPPL_REFERENCE_USERINFO')
alter table UserApply
   drop constraint FK_USERAPPL_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('business') and o.name = 'FK_BUSINESS_REFERENCE_DEPARTME')
alter table business
   drop constraint FK_BUSINESS_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('postInfo') and o.name = 'FK_POSTINFO_REFERENCE_WORKERIN')
alter table postInfo
   drop constraint FK_POSTINFO_REFERENCE_WORKERIN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('scheme') and o.name = 'FK_SCHEME_REFERENCE_DEPARTME')
alter table scheme
   drop constraint FK_SCHEME_REFERENCE_DEPARTME
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserApply')
            and   type = 'U')
   drop table UserApply
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('WorkerInfo')
            and   type = 'U')
   drop table WorkerInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('business')
            and   type = 'U')
   drop table business
go

if exists (select 1
            from  sysobjects
           where  id = object_id('departmentInfo')
            and   type = 'U')
   drop table departmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('postInfo')
            and   type = 'U')
   drop table postInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('scheme')
            and   type = 'U')
   drop table scheme
go

/*==============================================================*/
/* Table: UserApply                                             */
/*==============================================================*/
create table UserApply (
   ApplyId              int                  not null,
   userNo               int                  null,
   date                 datetime             null,
   day                  int                  null,
   addPerson            int                  null,
   address              nvarchar(80)         null,
   budget               int                  null,
   accept               nvarchar(80)         null,
   constraint PK_USERAPPLY primary key (ApplyId)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   userNo               int                  not null,
   bankNo               nvarchar(80)         null,
   phone                nvarchar(80)         null,
   address              nvarchar(80)         null,
   constraint PK_USERINFO primary key (userNo)
)
go

/*==============================================================*/
/* Table: WorkerInfo                                            */
/*==============================================================*/
create table WorkerInfo (
   wokerID              int                  not null,
   workerName           nvarchar(80)         null,
   postId               int                  not null,
   phone                nvarchar(80)         null,
   wage                 int                  null,
   constraint PK_WORKERINFO primary key (postId)
)
go

/*==============================================================*/
/* Table: business                                              */
/*==============================================================*/
create table business (
   depID                int                  not null,
   principal            nvarchar(80)         null,
   phone                nvarchar(80)         null,
   email                nvarchar(80)         null,
   constraint PK_BUSINESS primary key (depID)
)
go

/*==============================================================*/
/* Table: departmentInfo                                        */
/*==============================================================*/
create table departmentInfo (
   depID                int                  not null,
   depName              nvarchar(80)         null,
   constraint PK_DEPARTMENTINFO primary key (depID)
)
go

/*==============================================================*/
/* Table: postInfo                                              */
/*==============================================================*/
create table postInfo (
   postId               int                  not null,
   postName             nvarchar(80)         null,
   constraint PK_POSTINFO primary key (postId)
)
go

/*==============================================================*/
/* Table: scheme                                                */
/*==============================================================*/
create table scheme (
   depID                int                  not null,
   principal            nvarchar(80)         null,
   phone                nvarchar(80)         null,
   email                nvarchar(80)         null,
   constraint PK_SCHEME primary key (depID)
)
go

alter table UserApply
   add constraint FK_USERAPPL_REFERENCE_USERINFO foreign key (userNo)
      references UserInfo (userNo)
go

alter table business
   add constraint FK_BUSINESS_REFERENCE_DEPARTME foreign key (depID)
      references departmentInfo (depID)
go

alter table postInfo
   add constraint FK_POSTINFO_REFERENCE_WORKERIN foreign key (postId)
      references WorkerInfo (postId)
go

alter table scheme
   add constraint FK_SCHEME_REFERENCE_DEPARTME foreign key (depID)
      references departmentInfo (depID)
go

